IF EXISTS ( SELECT 'X' FROM sys.procedures WHERE name = 'InsLogonActivity' AND schema_id = 1 )
BEGIN
     IF EXISTS ( SELECT 'X' 
                 FROM   sys.server_triggers 
                 WHERE  parent_class_desc = 'SERVER' 
                 AND    name              = 'LogAllLogons' 
                 AND    is_disabled       = 0 )
     BEGIN
          EXEC( 'DISABLE TRIGGER [LogAllLogons] ON ALL SERVER' )
     END

     DROP PROCEDURE dbo.InsLogonActivity
END
GO

CREATE PROCEDURE dbo.InsLogonActivity
     @EventData xml 
WITH ENCRYPTION 
AS
/*
 *
 * InsLogonActivity
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/29/2009, 1.00.0000
 *
 */
BEGIN
     SET XACT_ABORT ON
     SET NOCOUNT ON
     
     DECLARE @SPID               int,
             @net_transport      varchar(40),
             @client_net_address varchar(48)

     SELECT @SPID = @EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

     SELECT @net_transport      = net_transport,
            @client_net_address = client_net_address
     FROM   sys.dm_exec_connections 
     WHERE  session_id = @SPID

     INSERT dbo.EventLog
          ( EventLogTypeId,
            HostName,
            LoginName,
            DatabaseUserName,
            DatabaseName,
            AppName,
            SPID,
            SID,
            LogByApp,
            LogByAppSubSystem,
            LogByDesc,
            LogText,
            MiscVC1,
            MiscVC2,
            MiscDt1,
            MiscTinyint1 )
     SELECT 1, -- EventLogTypeId
            HostName = CASE WHEN @EventData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(128)') = N'<local machine>'
                            THEN HOST_NAME()
                            ELSE @EventData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(128)')
                       END,
            @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),
            NULL, -- DatabaseUserName,
            NULL, -- DatabaseName
            APP_NAME(),
            @EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),
            @EventData.value('(/EVENT_INSTANCE/SID)[1]', 'varbinary(85)'),
            'LogAllLogon', -- LogByApp
            'InsLogonActivity', -- LogByAppSubSystem
            '', -- LogByDesc
            '', -- LogText
            @net_transport,
            MiscVC2 = CASE WHEN @client_net_address = '<local machine>'
                           THEN HOST_NAME()
                           ELSE @client_net_address
                      END,
            @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
            @EventData.value('(/EVENT_INSTANCE/IsPooled)[1]', 'tinyint')
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Inserts a logon activity row into the EventLog table, this sproc is called from the Server Level logon trigger.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = InsLogonActivity;
GO

GRANT EXEC ON dbo.InsLogonActivity TO AuditCredUser
GO

-- Enable the trigger
IF EXISTS ( SELECT 'X' 
            FROM   sys.server_triggers 
            WHERE  parent_class_desc = 'SERVER' 
            AND    name              = 'LogAllLogons' 
            AND    is_disabled       = 1 )
BEGIN
     EXEC ( 'ENABLE TRIGGER [LogAllLogons] ON ALL SERVER' )
END
GO
